package pl.leader100.rest.data;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import simplemysql.SimpleMySQL;

public class DocDao {

	private static DocDao instance;
	private SimpleMySQL mysql = new SimpleMySQL();

	private void setMysql() {
		mysql = new SimpleMySQL();
		mysql.connect("localhost:3306/l100", "l100", "warto");
	}

	public static DocDao GetInstance() {
		if (instance == null) {
			instance = new DocDao();

			SimpleMySQL mysql = new SimpleMySQL();
			try {
				mysql.connect("localhost:3306/l100", "l100", "warto");

				String sql = "SELECT * FROM downloadkey";
				ResultSet result = mysql.query(sql);

				while (result.next()) {

					if (Long.valueOf(result.getString("timestamp")) + 3600 * 1000 < new Date()
							.getTime()) {
						mysql.query("DELETE FROM downloadkey WHERE uniqueid='"
								+ result.getString("uniqueid") + "'");
					}
				}
			} catch (SQLException e) {
			} finally {
				mysql.close();
			}
		}
		return instance;
	}

	private Map<String, Doc> contentProvider = new HashMap<String, Doc>();

	public Doc getDocByIdKey(String id, String key) {
		SimpleMySQL mysql = new SimpleMySQL();
		try {
			mysql.connect("localhost:3306/l100", "l100", "warto");

			String sql = "SELECT timestamp FROM downloadkey WHERE (id ='" + id
					+ "' AND uniqueid ='" + key + "')";
			ResultSet result = mysql.query(sql);
			if (result == null)
				return null;

			if (result.next()) {
				if (Long.valueOf(result.getString("timestamp")) + 3600 * 1000 < new Date()
						.getTime()) {
					mysql.query("DELETE FROM downloadkey WHERE uniqueid='"
							+ key + "'");
					return null;
				} else {
					result = mysql
							.query("SELECT * FROM document WHERE iddocument='"
									+ id + "'");
					result.next();
					Doc doc = new Doc(result.getInt("iddocument"),
							result.getString("title"),
							result.getString("path"),
							result.getString("locale"),
							result.getString("course"),
							result.getString("category"),
							result.getString("mimetype"));
					return doc;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return null;
	}

	public List<Doc> getDocs(String role, int month, String locale,
			String course) {

		role = role.toUpperCase();
		locale = locale.toLowerCase();

		ArrayList<Doc> list = new ArrayList<Doc>();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		Date dat;

		setMysql();
		try {
			ResultSet result;
			String sql = "SELECT b.* FROM document as b WHERE a." + role + "=1"
					+ " AND b.month=" + month + " AND a.course='" + course
					+ "' AND b.locale='" + locale + "' ;";
			result = mysql.query(sql);

			if (result == null)
				return null;

			Doc doc = null;

			while (result.next()) {

				UUID uniqueKey = UUID.randomUUID();
				String id = result.getString("iddocument");
				sql = "INSERT INTO downloadkey VALUES('" + id + "', " + "'"
						+ uniqueKey.toString() + "', '"
						+ (new Date()).getTime() + "', '99')";
				mysql.query(sql);

				doc = new Doc(result.getString("title"),
						result.getString("path"), result.getString("locale"),
						result.getString("course"),
						result.getString("category"),
						result.getString("mimetype"));

				doc.setClasse(result.getString("class"));
				doc.setMonth(result.getInt("month"));
				doc.setParent(result.getBoolean("parent"));
				doc.setStudent(result.getBoolean("student"));
				doc.setTeacher(result.getBoolean("teacher"));
				doc.setCoach(result.getBoolean("coach"));
				doc.setTutor(result.getBoolean("tutor"));
				doc.setDirector(result.getBoolean("director"));
				doc.setIddocument(result.getInt("iddocument"));
				doc.setUniqueKey(uniqueKey);
				list.add(doc);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return list;
	}

	public List<Doc> getDocsOld(String role, String date, String locale,
			String year, String course) {

		role = role.toUpperCase();
		locale = locale.toLowerCase();

		ArrayList<Doc> list = new ArrayList<Doc>();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		Date dat;

		SimpleMySQL mysql = new SimpleMySQL();
		try {
			mysql.connect("localhost:3306/l100", "l100", "warto");

			ResultSet result;
			String sql = "SELECT b.* FROM schedule as a, document as b WHERE a.document = b.iddocument AND a."
					+ role
					+ "=1"
					+ " AND a.date1<='"
					+ date
					+ "' AND a.date2>='"
					+ date
					+ "' AND a.year='"
					+ year
					+ "' AND b.course='"
					+ course
					+ "' AND b.locale='"
					+ locale
					+ "' ;";
			result = mysql.query(sql);

			if (result == null)
				return null;

			Doc doc = null;

			while (result.next()) {

				UUID uniqueKey = UUID.randomUUID();
				String id = result.getString("iddocument");
				sql = "INSERT INTO downloadkey VALUES('" + id + "', " + "'"
						+ uniqueKey.toString() + "', '"
						+ (new Date()).getTime() + "', '99')";
				mysql.query(sql);

				doc = new Doc(result.getString("title"),
						result.getString("path"), result.getString("locale"),
						result.getString("course"),
						result.getString("category"),
						result.getString("mimetype"));
				doc.setIddocument(result.getInt("iddocument"));
				doc.setUniqueKey(uniqueKey);
				list.add(doc);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return list;
	}

	public List<Doc> getDocsByCat(String role, String date, String locale,
			String year, String course, String category) {

		role = role.toUpperCase();
		locale = locale.toLowerCase();

		ArrayList<Doc> list = new ArrayList<Doc>();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		Date dat;

		SimpleMySQL mysql = new SimpleMySQL();
		try {
			mysql.connect("localhost:3306/l100", "l100", "warto");

			ResultSet result;
			String sql = "SELECT b.* FROM schedule as a, document as b WHERE a.document = b.iddocument AND a."
					+ role
					+ "=1"
					+ " AND a.date1<='"
					+ date
					+ "' AND a.date2>='"
					+ date
					+ "' AND a.year='"
					+ year
					+ "' AND b.course='"
					+ course
					+ "' AND b.locale='"
					+ locale
					+ "' AND b.category='" + category + "' ;";
			result = mysql.query(sql);

			if (result == null)
				return null;

			Doc doc = null;

			while (result.next()) {

				UUID uniqueKey = UUID.randomUUID();
				String id = result.getString("iddocument");
				sql = "INSERT INTO downloadkey VALUES('" + id + "', " + "'"
						+ uniqueKey.toString() + "', '"
						+ (new Date()).getTime() + "', '99')";
				mysql.query(sql);

				doc = new Doc(result.getInt("iddocument"),
						result.getString("title"), result.getString("path"),
						result.getString("locale"), result.getString("course"),
						result.getString("category"),
						result.getString("mimetype"));
				doc.setUniqueKey(uniqueKey);
				list.add(doc);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return list;
	}

	public List<Doc> getDocsByCat(String role, int month, String locale,
			String course, String category) {

		role = role.toUpperCase();
		locale = locale.toLowerCase();

		ArrayList<Doc> list = new ArrayList<Doc>();

		SimpleMySQL mysql = new SimpleMySQL();
		try {
			mysql.connect("localhost:3306/l100", "l100", "warto");

			ResultSet result;
			String sql = "SELECT * FROM document WHERE " + role + "=1"
					+ " AND month=" + month + " AND course='" + course
					+ "' AND locale='" + locale + "' AND category='" + category
					+ "' ;";
			result = mysql.query(sql);

			if (result == null)
				return null;

			Doc doc = null;

			while (result.next()) {

				UUID uniqueKey = UUID.randomUUID();
				String id = result.getString("iddocument");
				sql = "INSERT INTO downloadkey VALUES('" + id + "', " + "'"
						+ uniqueKey.toString() + "', '"
						+ (new Date()).getTime() + "', '99')";
				mysql.query(sql);

				doc = new Doc(result.getInt("iddocument"),
						result.getString("title"), result.getString("path"),
						result.getString("locale"), result.getString("course"),
						result.getString("category"),
						result.getString("mimetype"));

				doc.setClasse(result.getString("class"));
				doc.setMonth(result.getInt("month"));
				doc.setParent(result.getBoolean("parent"));
				doc.setStudent(result.getBoolean("student"));
				doc.setTeacher(result.getBoolean("teacher"));
				doc.setCoach(result.getBoolean("coach"));
				doc.setTutor(result.getBoolean("tutor"));
				doc.setDirector(result.getBoolean("director"));
				doc.setIddocument(result.getInt("iddocument"));
				doc.setUniqueKey(uniqueKey);
				list.add(doc);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return list;
	}

	private DocDao() {

		SimpleMySQL mysql = new SimpleMySQL();
		try {
			mysql.connect("localhost:3306/l100", "l100", "warto");

			ResultSet result;
			String sql = "SELECT * FROM document";
			result = mysql.query(sql);

			if (result == null)
				return;

			Doc doc = null;

			while (result.next()) {
				doc = new Doc(result.getString("title"),
						result.getString("path"), result.getString("locale"),
						result.getString("course"),
						result.getString("category"),
						result.getString("mimetype"));
				doc.setIddocument(result.getInt("iddocument"));
				contentProvider.put(doc.getPath(), doc);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}

	}
	/*
	public String retrieveDoc(String path) {
		return "http://l100.wegielek.edu.pl/doc/"
				+ getModel().get(path).getLocale() + "/"
				+ getModel().get(path).getPath();
	}*/

	public String retrieveDoc(String path) {
		return "http://l100.wegielek.edu.pl/doc/"
				+ getModel().get(path).getLocale() + "/"
				+ getModel().get(path).getPath();
	}
	
	public Map<String, Doc> getModel() {
		return contentProvider;
	}

	/**
	 * return the class level based on course name
	 * 
	 * @param course
	 * @return
	 */
	public String getClass(String course) {
		String[] cl = { "SP5", "SP6", "1GIM", "2GIM", "3GIM", "1LIC", "2LIC",
				"3LIC" };
		String[] cou = { "pre_leader_r1", "pre_leader_r2", "junior_leader_r1",
				"junior_leader_r2", "senior_leader_r1", "senior_leader_r2",
				"college_leader_r1", "college_leader_r2" };
		for (int i = 0; i < 8; i++) {
			if (cou[i].equals(course)) {
				return cl[i];
			}
		}
		return null;
	}

	
	private String getCompetency(String field, int month, String locale, String year,
			String course) {
		SimpleMySQL mysql = new SimpleMySQL();
		try {
			mysql.connect("localhost:3306/l100", "l100", "warto");

			ResultSet result;
			String sql = "SELECT competency, habbit FROM competency WHERE "
					+ "locale='" + locale + "' AND course='" + course
					+ "' AND month=" + month + " ;";
			result = mysql.query(sql);

			if (result == null)
				return null;

			while (result.next()) {
				return result.getString(field);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return null;
	}

	public String[] getSituations(String field, int month, String locale, String year,
			String course) {

		String[] situation = new String[3];
		//String field = "competency";
		
		int sept = 9;
		int m;
		if (month >= 9)
			m = month - sept;
		else
			m = month + 3;
		
		if(m == 0) 
			situation[0] = getCompetency(field, 11, locale, year, course);
		else if (m > 0)
			situation[0] = getCompetency(field, m - 1, locale, year, course);
		
		situation[1] = getCompetency(field, m, locale, year, course);
		situation[2] = getCompetency(field, m + 1, locale, year, course);
		
		return situation;

	}

}
